SQL Tricks: For and For-Each Loops

Mark Leighton Fisher on 2006-05-12T16:25:26

Straight (non-procedural) SQL can perform simple For and For-Each loops. I didn't believe it myself, but reading Parsing CSV Values Into Multiple Rows showed me it could be done.

The trick lies in joining the table(s) of interest with a field whose values are the topic of the loop ($_ in Perl). If the field values are an unbroken simple sequence of numbers, you have a For loop. All other sets of topic values would be used in For-Each loops.

A simple example: Your local police have only 2 names for unknown perpetrators in their database, John Doe and Jane Doe, but needs 20 more names like John Doe #7 and Jane Doe #4. Using a field with values 1-10 as the loop topic ($_), you can create these additional names with SQL like:

insert into
   perpetrators
   (
      Name
   )
select
   (p.Name & ' #' & i.index) as Name
from
   perpetrators p, indexes i
where
   p.Name="John Doe" 
   or p.Name="Jane Doe";

Since this is a cross join (no fields are joined between the two tables), the rows returned by the SELECT will be the Cartesian product of the two sets of values:

Index  Name
1      John Doe
2      John Doe
...
10     John Doe
1      Jane Doe
2      Jane Doe
...
10     Jane Doe

As you can see from this example, it is easy to derive the new perpetrator names from this SELECT using the Perpetrators.Name and Indexes.Index fields.

Why you would want to do this in SQL, I don't know. It reminds me of XSLTunit, somehow... But it does show how SQL can be used for simple For and For-Each loops.

That’s not an outer join

Aristotle on 2006-05-12T21:12:15

An outer join is a join on a field which contains all rows from the left, right or both tables (left/right/full outer join, respectively). Wherever a row has to be included in the result set but could not be joined to a row in other the table, the other table’s column’s in that result set row are filled with NULLs.

A join that produces a Cartesian product is called a cross join. You can denote it explicitly in modern SQL dialects by saying something like

INSERT INTO perpetrators ( name ) SELECT ( p.name & ' #' & i.index ) AS name FROM perpetrators p CROSS JOIN indexes i WHERE p.name IN ( "John Doe", "Jane Doe" )

Cross Join, Not Outer Join: Thanks!

Mark Leighton Fisher on 2006-05-26T16:01:10